package com.tsky.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.RowSet;

import com.tsky.entry.TraceAddrObj;
import com.tsky.util.JdbcUtility;

public class TraceAddrInfoDao {
	private static final String DATASOURCENAME = "test";

	private Map<String,String> map = null;
	private RowSet rs = null;
	
	public TraceAddrInfoDao(){}
	
	/**
	 * 根据小区名分组
	 * @return
	 */
	public List<TraceAddrObj> getCellNameInfo(){
		List<TraceAddrObj> list = new ArrayList<TraceAddrObj>();
		String sql = "SELECT cell_name,(t.jd||'-'||t.wd) FROM user_info_data t "+
		 "GROUP BY cell_name,(t.jd||'-'||t.wd) ORDER BY cell_name";
		System.out.println(sql);
		try {
			rs = JdbcUtility.executeQuery(DATASOURCENAME, sql, null);
			while(rs.next()){
				TraceAddrObj obj = new TraceAddrObj();
				obj.setCellName(rs.getString(1));
				obj.setAddress(rs.getString(2));
				list.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public Map<String,String> getTraceAddrMap(String calling,String sdate,String edate){
		map = new HashMap<String, String>();
		String sql = "SELECT  /*+parallel(4) t.start_time,(t.jd||'-'||t.wd) addr FROM user_info_data t " +
		 "WHERE t.calling = ? "+
		 "AND t.start_time BETWEEN to_date(?,'yyyy-mm-dd hh24:mi:ss') "+
		 "AND to_date(?,'yyyy-mm-dd hh24:mi:ss') ORDER BY t.start_time ASC ";
		
		String sql2 = "SELECT  /*+parallel(4)*/ t.start_time,(t.jd||'-'||t.wd) addr FROM user_info_data t " +
		 "WHERE t.calling = '"+calling+"' "+
		 "AND t.start_time BETWEEN to_date('"+sdate+"','yyyy-mm-dd hh24:mi:ss') "+
		 "AND to_date('"+edate+"','yyyy-mm-dd hh24:mi:ss') ORDER BY t.start_time ASC ";
		String sql3 = "SELECT DISTINCT CALLING,start_time FROM user_info_data WHERE ROWNUM <10 ";
		String[] params = {calling,sdate,edate};
		System.out.println(sql2);
		try {
			rs = JdbcUtility.executeQuery(DATASOURCENAME, sql3, params);
			while(rs.next()){
				map.put(rs.getString(1),rs.getString(2));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return map;
	}
	
	
	public Map<String,String> getTraceAddrAllMap(String calling,String sdate,String edate,int pageIndex,int pageSize){
		map = new HashMap<String, String>();
		String sql = "SELECT  /*+parallel(4)*/ DISTINCT (t.jd||'-'||t.wd) AS addr,t.start_time FROM user_info_data t WHERE "+
					 "t.start_time BETWEEN to_date(?,'yyyy-mm-dd hh24:mi:ss') "+
					 "AND to_date(?,'yyyy-mm-dd hh24:mi:ss') "+
					 "GROUP BY t.start_time ,(t.jd||'-'||t.wd) "+
					 "ORDER BY t.start_time ASC ";
		String[] params = {calling,sdate,edate};
		
		try {
			rs = JdbcUtility.executeQuery(DATASOURCENAME, sql, params,pageIndex,pageSize);
			while(rs.next()){
				map.put(rs.getString(1),rs.getString(2));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return map;
	}
	
	public static void main(String[] args) {
		TraceAddrInfoDao dao = new TraceAddrInfoDao();
		System.out.println(dao.getCellNameInfo().size());
//		int size = dao.getTraceAddrMap("18818868038", "2013/10/19 6:00:00", "2013/10/20 23:23:00").size();
//		int size = dao.getTraceAddrAllMap("15920163374", "2013/10/19 6:00:00", "2013/10/20 23:23:00",2,10).size();
//		System.out.println(size);
	}
}
